<?php

include_once '../Connection.php';
include_once 'Database.php';

class ModelCreator_Database_SGBD_Oracle {

    private $conn;
    public $_test;
    public $_privileges = true;
    public $_allTables;
    public $_allColumns;
    public $_allPrimaryKeys;

    public function __construct($host, $port, $db, $user, $password, $schema, $arrTables = NULL) {
        $this->connect($host, $port, $db, $user, $password, $schema);
        if ($this->testConnection()) {
            $this->checkPrivileges($user);
            $this->getAllTables($schema, $arrTables);
            $this->getAllColumns($this->_allTables);
            $this->getPrimaryKeys($this->_allTables, $schema);
        }
        oci_close($this->conn);
    }
    

    private function connect($host, $port, $db, $user, $password) {
        $dbConnStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
            (HOST=" . $host . ")(PORT=" . $port . "))
            (CONNECT_DATA=(SID=" . $db . ")(SERVER = DEDICATED)))";
        $this->conn = oci_connect($user, $password, $dbConnStr);
        return $this->conn;
    }

    private function testConnection() {
        if (!$this->conn) {
            $this->_test = false;
            return FALSE;
        } else {
            $this->_test = true;
            return TRUE;
        }
    }
    private function checkPrivileges($user){
        $sql = "SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SIARCOV3'";
        $result = oci_parse($this->conn, $sql);
        oci_execute($result);

        if (!$result) {
            echo "An error occured.\n";
            exit;
        }
        $array = oci_fetch_array($result, OCI_ASSOC);
        if(empty($array)){
            $this->_privileges = false;
        }
    }
    public function getAllTables($schema, $arrTables) {
        $sql = "SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND SECONDARY != 'Y'";
        if (!isset($arrTables['allTables']) && $arrTables['allTables'] != 'ok' && !is_null($arrTables)) {
            unset($arrTables['allTables']);
            $sql .= " AND OBJECT_NAME IN (";
            $end = end($arrTables);
            foreach ($arrTables as $value) {
                if ($value == $end) {
                    $sql .= "'{$value}')";
                } else {
                    $sql .= "'{$value}',";
                }
            }
        }
        $result = oci_parse($this->conn, $sql);
        oci_execute($result);

        if (!$result) {
            echo "An error occured.\n";
            exit;
        }
        $i = 0;
        while ($array = oci_fetch_array($result, OCI_ASSOC)) {
            $arrayResult[$i] = $array['OBJECT_NAME'];
            $i++;
        }
        $this->_allTables = $arrayResult;
    }

    public function getAllColumns($tables) {
        foreach ($tables as $key => $value) {

            $sql = "SELECT COLUMN_NAME, DATA_TYPE
                    FROM ALL_TAB_COLUMNS
                    WHERE TABLE_NAME = '{$value}'";

            $result = oci_parse($this->conn, $sql);
            oci_execute($result);
            if (!$result) {
                echo "An error occured.\n";
                exit;
            }
            $i = 0;
            while ($array = oci_fetch_array($result, OCI_ASSOC)) {
                $arrayResult[$value][$i]['COLUMN'] = $array['COLUMN_NAME'];
                $arrayResult[$value][$i]['DATATYPE'] = $array['DATA_TYPE'];
                $i++;
            }
        }
        $this->_allColumns = $arrayResult;
    }

    public function getPrimaryKeys($tables, $schema) {
        $sql = "SELECT COLS.COLUMN_NAME, COLS.TABLE_NAME
                FROM ALL_CONSTRAINTS CONS 
                LEFT JOIN ALL_CONS_COLUMNS COLS
                ON CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
                WHERE CONS.CONSTRAINT_TYPE = 'P'
                AND CONS.OWNER = COLS.OWNER
                AND cols.owner = '" . strtoupper($schema) . "'
                ORDER BY COLS.TABLE_NAME";

        $result = oci_parse($this->conn, $sql);
        oci_execute($result);
        if (!$result) {
            echo "An error occured.\n";
            exit;
        }
        $array = oci_fetch_all($result, $output, null, null, OCI_FETCHSTATEMENT_BY_ROW);

        foreach ($tables as $value) {

            $i = 0;
            foreach ($output as $row) {
                if ($row['TABLE_NAME'] == $value) {
                    $arrayResult[$value][$i] = $row['COLUMN_NAME'];
                    $i++;
                }
                
            }
        }
        $this->_allPrimaryKeys = $arrayResult;
    }

}
